﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Collections.ObjectModel;
using System.Text;

[assembly: CLSCompliant(true)]

namespace Microsoft.InformationSecurity.CISF.Security.AuthZServices
{
    /// <summary>
    /// SQL data access object
    /// </summary>
    public static class AuthZDataAccess 
    {
        #region Methods

        #region GetDataReader
        /// <summary>
        /// Executes the provided SQL query and returns a collection of object arrays.
        /// </summary>
        /// <param name="connectionString">Connection string for the SQL server.</param>
        /// <param name="queryString">SQL query to execute.</param>
        /// <returns>Result of the query.
        /// </returns>
        public static Collection<object[]> GetSqlDataReader(string connectionString, string queryString)
        {
            ValidateInput(connectionString, queryString);

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                connection.Open();
                SqlDataReader sqlDataReader = command.ExecuteReader();

                Collection<object[]> dataRows = new Collection<object[]>();

                if (sqlDataReader.HasRows)
                {
                    while (sqlDataReader.Read())
                    {
                        object[] values = new object[sqlDataReader.FieldCount];
                        sqlDataReader.GetSqlValues(values);
                        dataRows.Add(values);
                    }
                }

                connection.Close();
                return dataRows;
            }
        }

        #endregion

        #region GetDataTable
        /// <summary>
        /// Executes the provided SQL query and returns the dataTable.
        /// </summary>
        /// <param name="connectionString">Connection string for the SQL server.</param>
        /// <param name="queryString">SQL query to execute.</param>
        /// <returns>Result of the query.
        /// </returns>
        public static DataTable GetSqlDataTable(string connectionString, string queryString)
        {
            ValidateInput(connectionString, queryString);

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, connection);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                connection.Close();

                return dataTable;
            }
        }

        #endregion

        #region ExecuteNonQueryTransaction
        /// <summary>
        /// Executes a batch of queries in a single database transaction.
        /// </summary>
        /// <param name="connectionString">Connection string for the SQL server.</param>
        /// <param name="queryStrings">A collection of SQL queries to execute.</param>
        public static void ExecuteNonQueryTransaction(string connectionString, Collection<string> queryStrings)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction = connection.BeginTransaction();

                command.Connection = connection;
                command.Transaction = transaction;

                try
                {
                    foreach(string queryString in queryStrings)
                    {
                        command.CommandText = queryString;
                        command.ExecuteNonQuery();
                    }

                    transaction.Commit();
                    connection.Close();
                }

                catch(Exception e)
                {
                    transaction.Rollback();
                    throw e;
                }
            }
        }

        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// Executes a single query with no return data set.
        /// </summary>
        /// <param name="connectionString">Connection string for the SQL server.</param>
        /// <param name="queryStrings">A SQL query to execute.</param>
        public static void ExecuteNonQuery(string connectionString, string queryString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();

            }
        }
        
        #endregion

        #region FormatFilter
        /// <summary>
        /// Formats the collection into a string list of filters which can be parsed by the 
        /// ISRM_ftStringToTable function.
        /// </summary>
        /// <param name="filters">A collection of filters.</param>
        /// <returns>Comma delimited string of filters.
        /// </returns>
        public static string FormatFilter(Collection<string> filters)
        {
            string inputFormat = "{0},";
            StringBuilder input = new StringBuilder();
            string filterOut = "";

            if (filters != null)
            {
                foreach (string filter in filters)
                    input.Append(String.Format(inputFormat, filter));

                filterOut = input.ToString();
            }

            return filterOut;
        }

        #endregion

        #region ValidateInput
        /// <summary>
        /// Validates the connection string and query string arguments.
        /// </summary>
        /// <param name="connectionString">Connection string for the SQL server.</param>
        /// <param name="queryString">SQL query to execute.</param>
        private static void ValidateInput(string connectionString, string queryString)
        {
            if (String.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("connectionString", "Argument is null or empty");

            if (String.IsNullOrEmpty(queryString))
                throw new ArgumentNullException("queryString", "Argument is null or empty");
        }

        #endregion

        #endregion
    }
}
